package com.yuqq;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.*;

public class ExcelDeduplication {
    public static void main(String[] args) {
        String inputFilePath = "src/main/resources/input.xlsx";
        String outputFilePath = "src/main/resources/output333.xlsx";

        try (InputStream inputStream = new FileInputStream(inputFilePath);
             Workbook workbook = new XSSFWorkbook(inputStream);
             OutputStream outputStream = new FileOutputStream(outputFilePath)) {

            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();

            // 用于存储已出现的行内容
            Set<String> seenRows = new HashSet<>();
            // 记录需要删除的行号
            List<Integer> rowsToDelete = new ArrayList<>();

            // 从第一行(index=0)开始处理
            for (int i = 0; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                String rowContent = getRowContentAsString(row);
                if (seenRows.contains(rowContent)) {
                    rowsToDelete.add(i);
                } else {
                    seenRows.add(rowContent);
                }
            }

            // 从后往前删除行，避免行号变化影响删除操作
            for (int i = rowsToDelete.size() - 1; i >= 0; i--) {
                int rowIndex = rowsToDelete.get(i);
                deleteRow(sheet, rowIndex);
            }

            workbook.write(outputStream);
            System.out.println("去重完成，结果已保存到: " + outputFilePath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 将行内容转换为字符串表示
    private static String getRowContentAsString(Row row) {
        StringBuilder sb = new StringBuilder();
        int lastCellNum = row.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            Cell cell = row.getCell(i);
            if (cell == null) {
                sb.append("null").append("\t");
            } else {
                switch (cell.getCellType()) {
                    case STRING:
                        sb.append(cell.getStringCellValue()).append("\t");
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            sb.append(cell.getDateCellValue()).append("\t");
                        } else {
                            sb.append(cell.getNumericCellValue()).append("\t");
                        }
                        break;
                    case BOOLEAN:
                        sb.append(cell.getBooleanCellValue()).append("\t");
                        break;
                    case FORMULA:
                        sb.append(cell.getCellFormula()).append("\t");
                        break;
                    default:
                        sb.append("").append("\t");
                }
            }
        }
        return sb.toString();
    }

    // 删除指定行
    private static void deleteRow(Sheet sheet, int rowIndex) {
        int lastRowNum = sheet.getLastRowNum();
        if (rowIndex >= 0 && rowIndex < lastRowNum) {
            sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
        }
        if (rowIndex == lastRowNum) {
            Row removingRow = sheet.getRow(rowIndex);
            if (removingRow != null) {
                sheet.removeRow(removingRow);
            }
        }
    }
}
